/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package TableModel;

import Connection.Koneksi;
import Entities.Kendaraan;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Adrian
 */
public class Kendaraan_List {

    private static Connection connection;
    private static Kendaraan_List kendaraanList;

    public Kendaraan_List(Connection connection) {
        //inisialisasi conn
        this.connection = connection;
    }

    public static Kendaraan_List getKendaraanList() {
        //untuk mengambil status koneksi dari kelas Koneksi
        if (kendaraanList == null) {
            kendaraanList = new Kendaraan_List(Koneksi.getConnection());
        }
        return kendaraanList;
    }

    public List<Kendaraan> getAllKendaraan() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "SELECT * FROM kendaraan";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Kendaraan> kendaraans = new ArrayList<Kendaraan>();

        while (resultSet.next()) {
            try {
                Kendaraan kendaraan = new Kendaraan();

                kendaraan.setNo_polisi(resultSet.getString(1));
                kendaraan.setMerk_kendaraan(resultSet.getString(2));
                kendaraan.setTipe_kendaraan(resultSet.getString(3));
                kendaraan.setWarna_kendaraan(resultSet.getString(4));
                kendaraan.setSpesifikasi_kendaraan(resultSet.getString(5));
                kendaraan.setStatus_kendaraan(resultSet.getString(6));
                kendaraan.setHarga_Sewa_Per_Hari(Double.parseDouble(resultSet.getString(7)));
                kendaraans.add(kendaraan);
            } catch (Exception ex) {
                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return kendaraans;
    }
    
    public List<Kendaraan> getAllKendaraanServis() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "SELECT * FROM kendaraan where status_kendaraan = 'SERVIS' ";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Kendaraan> kendaraans = new ArrayList<Kendaraan>();

        while (resultSet.next()) {
            try {
                Kendaraan kendaraan = new Kendaraan();

                kendaraan.setNo_polisi(resultSet.getString(1));
                kendaraan.setMerk_kendaraan(resultSet.getString(2));
                kendaraan.setTipe_kendaraan(resultSet.getString(3));
                kendaraan.setWarna_kendaraan(resultSet.getString(4));
                kendaraan.setSpesifikasi_kendaraan(resultSet.getString(5));
                kendaraan.setStatus_kendaraan(resultSet.getString(6));
                kendaraan.setHarga_Sewa_Per_Hari(Double.parseDouble(resultSet.getString(7)));
                kendaraans.add(kendaraan);
            } catch (Exception ex) {
                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return kendaraans;
    }
    
    public List<Kendaraan> getAllKendaraanSiapDisewa() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "SELECT * FROM kendaraan where status_kendaraan ='SIAP SEWA'";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Kendaraan> kendaraans = new ArrayList<Kendaraan>();

        while (resultSet.next()) {
            try {
                Kendaraan kendaraan = new Kendaraan();

                kendaraan.setNo_polisi(resultSet.getString(1));
                kendaraan.setMerk_kendaraan(resultSet.getString(2));
                kendaraan.setTipe_kendaraan(resultSet.getString(3));
                kendaraan.setWarna_kendaraan(resultSet.getString(4));
                kendaraan.setSpesifikasi_kendaraan(resultSet.getString(5));
                kendaraan.setStatus_kendaraan(resultSet.getString(6));
                kendaraan.setHarga_Sewa_Per_Hari(Double.parseDouble(resultSet.getString(7)));
                kendaraans.add(kendaraan);
            } catch (Exception ex) {
                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return kendaraans;
    }

    public List<Kendaraan> getAllKendaraanDistinct() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "SELECT DISTINCT merk_kendaraan FROM kendaraan";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Kendaraan> kendaraans = new ArrayList<Kendaraan>();

        while (resultSet.next()) {
            try {
                Kendaraan kendaraan = new Kendaraan();
                // kendaraan.setNo_polisi(resultSet.getString(1));
                kendaraan.setMerk_kendaraan(resultSet.getString(1));
                // kendaraan.setTipe_kendaraan(resultSet.getString(3));
                // kendaraan.setWarna_kendaraan(resultSet.getString(4));
                // kendaraan.setStatus_kendaraan(resultSet.getString(5));
                //kendaraan.setTanggal_servis(resultSet.getString(6));
                kendaraans.add(kendaraan);
            } catch (Exception ex) {
                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return kendaraans;
    }

    public List<Kendaraan> getKendaraanDanTanggalServis(String merk) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ResultSet resultSet2 = null;

        connection.setAutoCommit(false);
        String sql = "select distinct k.no_polisi, k.tipe_kendaraan, k.warna_kendaraan, s.tanggal_servis from kendaraan k, servis s where k.no_polisi=s.no_polisi and s.tanggal_servis>sysdate and k.status_kendaraan = 'SIAP SEWA' and k.merk_kendaraan='" + merk + "'";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Kendaraan> kendaraans = new ArrayList<Kendaraan>();

//        if (resultSet.toString().matches(null)) {
//        String sql2 = "select distinct no_polisi, tipe_kendaraan from kendaraan where merk_kendaraan='" + merk + "'";
//        preparedStatement = connection.prepareStatement(sql2);
//        resultSet2 = preparedStatement.executeQuery();
//        while (resultSet2.next()) {
//            try {
//                Kendaraan kendaraan = new Kendaraan();
//                kendaraan.setNo_polisi(resultSet2.getString(1));
//                kendaraan.setTipe_kendaraan(resultSet2.getString(2));
//                kendaraan.setTanggal_servis("");
//                kendaraans.add(kendaraan);
//            } catch (Exception ex) {
//                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
//            }
//        }
//        }

//        Kendaraan kendaraan=new Kendaraan();
//        while (resultSet.next()) {
//
//            kendaraan.setTanggal_servis(resultSet.getString(3));
//        }

//        if (!kendaraan.getTanggal_servis().matches(null)) {
        while (resultSet.next()) {
            try {
                Kendaraan kendaraan = new Kendaraan();
                kendaraan.setNo_polisi(resultSet.getString(1));
                kendaraan.setTipe_kendaraan(resultSet.getString(2));
                kendaraan.setWarna_kendaraan(resultSet.getString(3));
                kendaraan.setTanggal_servis(resultSet.getString(4));
                kendaraans.add(kendaraan);
            } catch (Exception ex) {
                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return kendaraans;
//        } else {
//            sql = "select no_polisi, tipe_kendaraan from kendaraan where status_kendaraan='Siap Sewa' and merk_kendaraan='" + merk + "'";
//            preparedStatement = connection.prepareStatement(sql);
//            resultSet = preparedStatement.executeQuery();
//            kendaraans = new ArrayList<Kendaraan>();
//            while (resultSet.next()) {
//                Kendaraan kendaraan = new Kendaraan();
//                kendaraan.setNo_polisi(resultSet.getString(1));
////            kendaraan.setMerk_kendaraan(resultSet.getString(2));
//                kendaraan.setTipe_kendaraan(resultSet.getString(2));
////            kendaraan.setWarna_kendaraan(resultSet.getString(4));
////            kendaraan.setStatus_kendaraan(resultSet.getString(5));
//                kendaraan.setTanggal_servis("");
//
//                kendaraans.add(kendaraan);
//            }
//            connection.commit();
//            return kendaraans;
//        }

    }

    public List<Kendaraan> getKendaraanDanTanggalKembali() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "select k.merk_kendaraan, k.tipe_kendaraan, k.no_polisi, k.status_kendaraan, sw.tanggal_kembali from kendaraan k, sewa sw where k.no_polisi=sw.no_polisi";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Kendaraan> kendaraans = new ArrayList<Kendaraan>();

        while (resultSet.next()) {
            try {
                Kendaraan kendaraan = new Kendaraan();
                kendaraan.setNo_polisi(resultSet.getString(3));
                kendaraan.setMerk_kendaraan(resultSet.getString(1));
                kendaraan.setTipe_kendaraan(resultSet.getString(2));
                kendaraan.setStatus_kendaraan(resultSet.getString(4));
                kendaraan.setTanggal_kembali(resultSet.getString(5));
                kendaraans.add(kendaraan);
            } catch (Exception ex) {
                Logger.getLogger(Kendaraan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return kendaraans;
    }
}
